import pandas as pd
import numpy as np
from sklearn.metrics import accuracy_score, mean_squared_error, r2_score, mean_squared_log_error, mean_absolute_error
from sklearn.preprocessing import StandardScaler,MinMaxScaler
from scipy.spatial import distance
from sklearn.tree import DecisionTreeRegressor
from sklearn.linear_model import LinearRegression, SGDRegressor
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor, AdaBoostRegressor, BaggingRegressor, ExtraTreesRegressor
import sklearn.model_selection as ms
from sklearn.neighbors import KNeighborsRegressor
import xgboost as xgb
from sklearn.linear_model import Lasso, Ridge, ElasticNet
import datetime
from SKO.AbstractDPJob import AbstractDPJob
class Model_ranliaobi3Job(AbstractDPJob):
    def __init__(self,
                 p_bf_no=None):

        super(Model_ranliaobi3Job, self).__init__()
        self.bf_no = p_bf_no
        pass


    def execute(self):
        return self.do_execute()


    def do_execute(self):
        super(Model_ranliaobi3Job, self).do_execute()
        #预测铁水硫接口传入参数

        bf_no = self.bf_no
        start = datetime.datetime.now()
        # gamma_list = [1.5, 3]
        gamma = 1.5
        neighbors_min = 5
        neighbors_max = 10
        guiyihua_list = ['MinMaxScaler', 'StandardScaler']
        modelname_list = ['Linear', 'XGB', 'RandomForest', 'KNeighbors',  'DecisionTree']
        # , 'KNeighbors'
        # gamma = 1.5
        # guiyihua = 'StandardScaler'
        # neighbors_num = 4
        # modelname = 'Linear'
        # 铁水硫
        xlsx_name = 'D:/repos/sicost/fe_s_' + str(bf_no) + '.xlsx'
        df1 = pd.read_excel(xlsx_name)
        df1.columns = df1.columns.str.upper()
        df1['PROD_DATE'] = df1['PROD_DATE'].astype(str)
        df1.drop(['UNIT_NO'], axis=1, inplace=True)
        # 燃料比
        xlsx_name = 'D:/repos/sicost/ranliaobi_' + str(bf_no) + '.xlsx'
        df2 = pd.read_excel(xlsx_name)
        df2.columns = df2.columns.str.upper()
        df2['PROD_DATE'] = df2['PROD_DATE'].astype(str)
        df2.drop(['UNIT_NO'], axis=1, inplace=True)
        df2.drop(['PROC_UNIT'], axis=1, inplace=True)
        # 喷吹煤硫
        xlsx_name = 'D:/repos/sicost/penchuimei_' + str(bf_no) + '.xlsx'
        df3 = pd.read_excel(xlsx_name)
        df3.columns = df3.columns.str.upper()
        df3['MAT_SAMPLE_DATE'] = df3['MAT_SAMPLE_DATE'].astype(str)
        df3.rename(columns={'MAT_SAMPLE_DATE': 'PROD_DATE'}, inplace=True)
        # 焦炭硫
        xlsx_name = 'D:/repos/sicost/jiaotan.xlsx'
        df4 = pd.read_excel(xlsx_name)
        df4.columns = df4.columns.str.upper()
        df4['MAT_SAMPLE_DATE'] = df4['MAT_SAMPLE_DATE'].astype(str)
        df4.rename(columns={'MAT_SAMPLE_DATE': 'PROD_DATE'}, inplace=True)
        # 数据关联
        v = ['PROD_DATE']
        df_12 = pd.merge(df1, df2, on=v, how='left')
        df_123 = pd.merge(df_12, df3, on=v, how='left')
        df_1234 = pd.merge(df_123, df4, on=v, how='left')

        # 数据清洗
        # 去除0和nan
        def clean_data(df, gamma):
            column_name_list = df.columns.tolist()
            column_name_list.remove('PROD_DATE')
            column_name_num = len(column_name_list)
            clean_str_start = 'df_new = df['
            clean_str_end = ']'
            ldict1 = {}
            for i in range(0, column_name_num):
                print(i)
                print(column_name_list[i])
                column_name_tmp = column_name_list[i]
                exec("q1_{} = df['{}'].quantile(0.25)".format(i, column_name_tmp), locals(), ldict1)
                exec("q3_{} = df['{}'].quantile(0.75)".format(i, column_name_tmp), locals(), ldict1)
                exec("iqr_val_{} = q3_{} - q1_{}".format(i, i, i), locals(), ldict1)
                exec(
                    '''clean_str1 = "(df['{}'] >= ldict1['q1_{}'] - gamma * ldict1['iqr_val_{}'])"'''.format(
                        column_name_tmp, i,
                        i), locals(),
                    ldict1)
                exec('''clean_str2 = "(df['{}'] < ldict1['q3_{}'] + gamma * ldict1['iqr_val_{}'])"'''.format(
                    column_name_tmp, i,
                    i), locals(),
                    ldict1)
                exec('''clean_str3 = "(df['{}'] > 0)"'''.format(column_name_tmp), locals(), ldict1)
                clean_str1 = ldict1["clean_str1"]
                clean_str2 = ldict1["clean_str2"]
                clean_str3 = ldict1["clean_str3"]
                if i == 0:
                    clean_str_start = clean_str_start + clean_str1 + ' & ' + clean_str2 + ' & ' + clean_str3
                else:
                    clean_str_start = clean_str_start + ' & ' + clean_str1 + ' & ' + clean_str2 + ' & ' + clean_str3
            clean_str = clean_str_start + clean_str_end
            print(clean_str)
            exec(clean_str, locals(), ldict1)
            df_new = ldict1["df_new"]
            df_new = df_new.reset_index(drop=True)
            return df_new

        df_clean_nan = df_1234.dropna()
        df_clean_0 = df_clean_nan[df_clean_nan.ne(0).all(axis=1)]


        # df0_clean = clean_data(df_1234, gamma)
        # df0_clean2 = clean_data(df_cleaned, gamma)
        df_clean_liqun = clean_data(df_clean_0, gamma)
        df_clean_liqun.drop(['SUM_CACULATE_IRON_WGT'], axis=1, inplace=True)
        df0 = df_clean_liqun.copy()
        df0['ZONGJIAOBI'] = df0['JIAOBI'] + df0['XIAOJIAOBI']
        df0['JIAOTAN_DAIRULIU'] = df0['JIAOTAN_S'] * df0['ZONGJIAOBI']
        df0['PENCHUIMEI_DAIRULIU'] = df0['PENCHUIMEI_S'] * df0['MEIBI']
        df0['KUANGSHI_DAIRULIU'] = df0['COMPUTE_FILL_S_VALUE'] - df0['JIAOTAN_DAIRULIU'] - df0['PENCHUIMEI_DAIRULIU']
        q1 = df0['KUANGSHI_DAIRULIU'].quantile(0.25)
        q3 = df0['KUANGSHI_DAIRULIU'].quantile(0.75)
        iqr_val = q3 - q1
        df0_new = df0[
            (df0['KUANGSHI_DAIRULIU'] < q3 + gamma * iqr_val) & (df0['KUANGSHI_DAIRULIU'] >= q1 - gamma * iqr_val) & (
                    df0['KUANGSHI_DAIRULIU'] > 0)]
        df0_new = df0_new.reset_index(drop=True)
        df0 = df0_new.copy()

        df0['PROD_DATE'] = df0['PROD_DATE'].astype(str)
        today = datetime.date.today()
        if today.month == 1:
            last_month_year = today.year - 1
            last_month = 12
        else:
            last_month_year = today.year
            last_month = today.month
        last_day_of_last_month = datetime.date(last_month_year, last_month, 1) - datetime.timedelta(days=1)
        p_day_1 = last_day_of_last_month.strftime("%Y%m%d")
        p_day_2 = p_day_1[0:6] + '01'
        df0_train = df0[(df0['PROD_DATE'] < p_day_1)]
        df0_train = df0_train.reset_index(drop=True)
        # df0_train.drop(['UNIT_NO'], axis=1, inplace=True)
        df_model_score = pd.DataFrame(
            columns=['GAMMA', 'NEIGHBORS_NUM', 'GUIYIHUA', 'MODELNAME', 'SCORE'])
        # , 'mae', 'rmse', 'r2'
        dict_score = {}

        df0_train_new = df0_train.copy()
        df0_test = df0_train_new[(df0_train_new['PROD_DATE'] < p_day_1) & (df0_train_new['PROD_DATE'] >= p_day_2)]
        df0_test_copy = df0_test.copy()
        df0_test_copy = df0_test_copy.reset_index(drop=False)
        df0_test_copy.rename(columns={'index': 'index_old'}, inplace=True)
        df0_test_merge = df0_test_copy[['index_old', 'JIAOTAN_DAIRULIU', 'PENCHUIMEI_DAIRULIU']]

        df03_new = df0_train_new.copy()
        df03_new.drop(['PROD_DATE'], axis=1, inplace=True)
        df03_new.drop(['AVG_S_VALUE'], axis=1, inplace=True)
        df03_new.drop(['AVG_C_S_VALUE'], axis=1, inplace=True)
        df03_new.drop(['COMPUTE_FILL_S_VALUE'], axis=1, inplace=True)
        df03_new.drop(['JIAOBI'], axis=1, inplace=True)
        df03_new.drop(['XIAOJIAOBI'], axis=1, inplace=True)
        df03_new.drop(['PENCHUIMEI_S'], axis=1, inplace=True)
        df03_new.drop(['JIAOTAN_S'], axis=1, inplace=True)
        df03_new.drop(['ZONGJIAOBI'], axis=1, inplace=True)
        df03_new.drop(['MEIBI'], axis=1, inplace=True)
        df03_new.drop(['KUANGSHI_DAIRULIU'], axis=1, inplace=True)
        df0_train_clean = df03_new.copy()
        df0_train_clean_Y = df0_train_clean[['JIAOTAN_DAIRULIU', 'PENCHUIMEI_DAIRULIU']]

        df0_train_new_X = df0_train_clean.drop(labels=['JIAOTAN_DAIRULIU', 'PENCHUIMEI_DAIRULIU'], axis=1, inplace=False)

        for guiyihua in guiyihua_list:
            if guiyihua == 'StandardScaler':
                transfer = StandardScaler()
            else:
                transfer = MinMaxScaler()
            X_trans = transfer.fit_transform(df0_train_new_X)
            df0_X_trans = pd.DataFrame(X_trans)
            df0_X_trans_copy = df0_X_trans.copy()
            df0_X_trans_copy = df0_X_trans_copy.reset_index(drop=False)
            df0_X_trans_copy.rename(columns={'index': 'index_old'}, inplace=True)
            v = ['index_old']
            df_merge = pd.merge(df0_test_merge, df0_X_trans_copy, on=v, how='left')
            test_index_list = df_merge['index_old'].tolist()
            for i in range(neighbors_min, neighbors_max + 1):
                print(i)
                neighbors_num = i * 2
                for modelname in modelname_list:
                    print(modelname)
                    y1_pred_list = []
                    y2_pred_list = []
                    for test_index_tmp in test_index_list:
                        # print(test_index_tmp)
                        df0_X_trans_copy2 = df0_X_trans.copy()
                        input_data_X = df0_X_trans_copy2.iloc[test_index_tmp].tolist()
                        input_X = df0_X_trans_copy2.iloc[-1:]
                        df0_X_new = df0_X_trans_copy2
                        df0_X_new['distance'] = df0_X_new.apply(lambda row: distance.euclidean(row, input_data_X),
                                                                axis=1)
                        df0_X_new['y1'] = df0_train_clean_Y['JIAOTAN_DAIRULIU']
                        df0_X_new['y2'] = df0_train_clean_Y['PENCHUIMEI_DAIRULIU']

                        df_sorted = df0_X_new.sort_values(by='distance')
                        df_sorted = df_sorted.reset_index(drop=True)
                        df_head = df_sorted.head(neighbors_num)
                        if modelname == 'Linear':
                            model = LinearRegression()
                        elif modelname == 'XGB':
                            model = xgb.XGBRegressor()
                        elif modelname == 'RandomForest':
                            model = RandomForestRegressor()
                        elif modelname == 'GradientBoosting':
                            model = GradientBoostingRegressor()
                        elif modelname == 'AdaBoost':
                            model = AdaBoostRegressor()
                        elif modelname == 'Bagging':
                            model = BaggingRegressor()
                        elif modelname == 'DecisionTree':
                            model = DecisionTreeRegressor()
                        elif modelname == 'ExtraTrees':
                            model = ExtraTreesRegressor()
                        elif modelname == 'KNeighbors':
                            model = KNeighborsRegressor()
                        elif modelname == 'Lasso':
                            model = Lasso(alpha=0.1)
                        elif modelname == 'Ridge':
                            model = Ridge(alpha=1.0)
                        elif modelname == 'ElasticNet':
                            model = ElasticNet(alpha=1.0, l1_ratio=0.5)
                        else:
                            model = LinearRegression()
                        X = df_head.drop(labels=['distance', 'y1', 'y2'], axis=1, inplace=False).values
                        y = df_head[['y1', 'y2']].values
                        X_input = input_X.values
                        model.fit(X, y)
                        y_pred = model.predict(X_input)
                        # print(y_pred)
                        # print(float(y_pred))
                        y1_pred = y_pred[0][0]
                        y2_pred = y_pred[0][1]
                        # print(y1_pred)
                        # print(y2_pred)
                        y1_pred_list.append(float(y1_pred))
                        y2_pred_list.append(float(y2_pred))
                    # y_pred_array = np.concatenate((np.array(y1_pred_list), np.array(y2_pred_list)))
                    y_pred_array_t = np.array([y1_pred_list, y2_pred_list])
                    y_pred_array = y_pred_array_t.T

                    y_true_array = df_merge[['JIAOTAN_DAIRULIU', 'PENCHUIMEI_DAIRULIU']].values

                    # y_true_list1 = df_merge['ZONGJIAOBI'].tolist()
                    # y_true_list2 = df_merge['MEIBI'].tolist()
                    # y_true_array = np.concatenate((np.array(y_true_list1), np.array(y_true_list2)))
                    # y_true_array = df_merge[['ZONGJIAOBI', 'MEIBI']].values

                    # 计算模型的准确度
                    MSE = mean_squared_error(y_true_array, y_pred_array)
                    # MAE = mean_absolute_error(y_true_list, y_pred_list)
                    # RMSE = np.sqrt(mean_squared_error(y_true_list, y_pred_list))
                    # RMSLE = np.sqrt(mean_squared_log_error(y_true_list, y_pred_list))
                    # R2 = r2_score(y_true_list, y_pred_list)
                    # print(f"Model Accuracy: {MSE}")
                    dict_score['GAMMA'] = gamma
                    dict_score['NEIGHBORS_NUM'] = neighbors_num
                    dict_score['GUIYIHUA'] = guiyihua
                    dict_score['MODELNAME'] = modelname
                    dict_score['SCORE'] = MSE
                    # dict_score['mae'] = MAE
                    # dict_score['rmse'] = RMSE
                    # dict_score['r2'] = R2
                    new_row = pd.Series(dict_score)
                    df_model_score = df_model_score.append(new_row, ignore_index=True)
        # writer = pd.ExcelWriter('model_score_0507.xlsx')
        # df_model_score.to_excel(writer, sheet_name='Sheet1', index=False)
        # writer.save()
        # df_model_score.rename(columns={'MSE': 'SCORE'}, inplace=True)
        df_model_sorted = df_model_score.sort_values(by='SCORE')
        # , ascending = False
        df_model_sorted = df_model_sorted.reset_index(drop=True)
        df_model_head = df_model_sorted.head(1)
        # 存到数据库
        writer = pd.ExcelWriter('model_score_0511_3.xlsx')
        df_model_sorted.to_excel(writer, sheet_name='Sheet1', index=False)
        writer.save()
        elapsed = float((datetime.datetime.now() - start).seconds)
        print("Time Used 4 All ----->>>> %f seconds" % (elapsed))
        print('finish')
        msg = '成功'
        return msg, elapsed

